import os
import sqlite3
import MySQLdb

class Sqlite:
	@staticmethod
	def executeNonQuery(file,command):
		database = sqlite3.connect(file)
		cursor = database.cursor()
	
		cursor.execute(command)
	
		cursor.close()
		database.close()
				
	@staticmethod
	def executeTable(file,command):
		database = sqlite3.connect(file)
		cursor = database.cursor()
	
		cursor.execute(command)
		#print dir(cursor)
		headers = [description[0] for description in cursor.description]
		tables = cursor.fetchall()
	
		cursor.close()
		database.close()
		return [headers,tables]

class Mysql:
	@staticmethod
	def executeNonQuery(configuration,command):
		database = MySQLdb.connect(host=configuration['host'],user=configuration['user'],passwd=configuration['passwd'],db=configuration['db'],charset=configuration['charset'])
		cursor = database.cursor()
		
		cursor.execute(command)
		
		database.commit()
		cursor.close()
		database.close()
	

class WindvoiceExportor:
	source = ''
	target = {}
	
	def __init__(self):
		self.source = '/var/data/windvoice1.db'
		self.target = {'host':'192.168.0.103', 'user':'root', 'passwd':'sa', 'db':'windvoice','charset':'utf8'}

	def getTables(self):
		command = 'select name from sqlite_master where type="table"'
		tables = Sqlite.executeTable(self.source,command)
		return [row[0] for row in tables]

	def export(self,table):
		#os.system("sqlite3 " + self.database + " '.header ON'")
		command = 'select * from ' + table
		result = Sqlite.executeTable(self.source,command)
		
		Mysql.executeNonQuery(self.target,'delete from '+table+';')
		
		for row in result[1]:
			index = 0
			command = 'insert into ' + table + ' (' + str(result[0]).lstrip('[').rstrip(']').replace("'","") + ') values ('
			for cell in [cell.encode('utf8') if isinstance(cell, unicode) else cell for cell in row]:
				if type(cell) == type(1):
					command += str(cell)
				else:
					command += "'"+str(cell).replace("'","''")+"'"
						
				if index != len(row)-1:
					command += ','
					
				index += 1
			command += ');'
			print command
			Mysql.executeNonQuery(self.target,command)
			print '--'
		
		
	


if __name__=='__main__':

	importor = WindvoiceExportor()
	importor.export('Article')
	
	'''
	for row in importor.export('link'):
		for value in row:
			print type(value)#.encode('utf8')
	
	for table in importor.getTables():
		importor.export(table)
		
	insert into Link ('ID', 'Content', 'LinkUrl', 'Type', 'Title', 'IsEnable', 'CreateTime', 'Author') values (4,'There is a Sheldon deep in every geeks heart.','http://www.cnblogs.com/',1,'cnblogs',1,'2011-08-15 16:36:50','Aries');

	'''